<?php
namespace app\api\controller;
use app\common\controller\Api;
use think\Db;
use think\Log;
use app\admin\model\UserRegisterDay;
use app\admin\model\UserLoginDay;
use app\admin\model\UserKeepDay;
use app\admin\model\UserSignDay;

/**
 * 定时任务
 */
class Timedtasks extends Api
{
	protected $noNeedLogin = ['*'];
	protected $noNeedRight = ['*'];
	
	// 每日00:05执行
	public function initDay() {
		/*$this->initUserRegisterDay();
		$this->initUserLoginDay();
		$this->initUserRegisterDayKeep();
		$this->initUserLoginDayKeep();
		$this->initScaleAll();*/
		$this->keep();
	}
	
	/**
	 * 每日注册人数
	 */
	public function initUserRegisterDay() {
		$sql = "SELECT DATE_FORMAT( from_unixtime( createtime ), '%Y-%m-%d' ) AS tim,app_id,count(*) AS num FROM sdk_user WHERE DATEDIFF( now(), DATE_FORMAT( from_unixtime( createtime ), '%Y-%m-%d' ) ) = 1 GROUP BY tim,app_id";
		$list = Db::query($sql);
		foreach($list as $key => $value) {
			$row[] = [
				'day_register' => $value['tim'],
				'app_id' => $value['app_id'],
				'num' => $value['num'],
				'createtime' => time(),
				'type' => '0'
			];
		}
		UserKeepDay::insertAll($row);
	}
	
	/**
	 * 每日登录人数
	 */
	public function initUserLoginDay() {
		// $sql = "SELECT DISTINCT DATE_FORMAT( temp.addtime, '%Y-%m-%d' ) AS tim,temp.app_id,count(*) AS num FROM ( SELECT DISTINCT user_id, app_id, addtime FROM sdk_user_login_log WHERE DATEDIFF( now(), DATE_FORMAT( addtime, '%Y-%m-%d' ) ) = 1 ) AS temp WHERE DATEDIFF( now(), DATE_FORMAT( addtime, '%Y-%m-%d' ) ) = 1 GROUP BY tim,temp.app_id";
		$sql = "SELECT DISTINCT tim,temp.app_id,count(*) AS num FROM (SELECT DISTINCT	user_id,app_id,DATE_FORMAT( addtime, '%Y-%m-%d' ) AS tim FROM	sdk_user_login_log WHERE DATEDIFF( now(), DATE_FORMAT( addtime, '%Y-%m-%d' ) ) = 1 ) AS temp GROUP BY tim,temp.app_id";
		$list = Db::query($sql);
		foreach($list as $key => $value) {
			$row[] = [
				'day_register' => $value['tim'],
				'app_id' => $value['app_id'],
				'num' => $value['num'],
				'createtime' => time(),
				'type' => '1'
			];
		}
		UserKeepDay::insertAll($row);
	}
	
	/**
	 * 新增留存人数
	 */
	public function initUserRegisterDayKeep() {
		$sql = "SELECT DATE_FORMAT( temp.createtime, '%Y-%m-%d' ) AS tim,temp.app_id,count(*) AS num,DATEDIFF( DATE_FORMAT( temp.addtime, '%Y-%m-%d' ), DATE_FORMAT( temp.createtime, '%Y-%m-%d' ) ) + 1 AS days FROM (SELECT b.*,a.addtime FROM (SELECT DISTINCT user_id, app_id, addtime FROM sdk_user_login_log WHERE DATEDIFF( now(), DATE_FORMAT( addtime, '%Y-%m-%d' ) ) = 1 ) AS a,( SELECT DISTINCT	id,app_id,DATE_FORMAT( from_unixtime( createtime ), '%Y-%m-%d' ) AS createtime FROM	sdk_user WHERE DATEDIFF( now(), DATE_FORMAT( from_unixtime( createtime ), '%Y-%m-%d' ) ) IN (2,3,4,5,6,7,15,30,60,90) ) AS b WHERE a.user_id = b.id AND a.app_id = b.app_id ) AS temp GROUP BY tim,temp.app_id";
		$list = Db::query($sql);
		foreach($list as $key => $value) {
			$where['day_register'] = $value['tim'];
			$where['app_id'] = $value['app_id'];
			$where['type'] = "0";
			$daystr = "day".$value['days'];
			$num = $value['num'];
			$re = UserKeepDay::where($where)->update([$daystr=>$num]);
		}
	}
	
	/**
	 * 活跃留存人数
	 */
	public function initUserLoginDayKeep() {
		$sql = "SELECT DATE_FORMAT( temp.addtime, '%Y-%m-%d' ) AS tim,temp.app_id,count(*) AS num,DATEDIFF( DATE_FORMAT( temp.atime, '%Y-%m-%d' ), DATE_FORMAT( temp.addtime, '%Y-%m-%d' ) ) + 1 AS days FROM (SELECT	b.*,a.addtime AS atime FROM ( SELECT DISTINCT user_id, app_id, addtime FROM sdk_user_login_log WHERE DATEDIFF( now(), DATE_FORMAT( addtime, '%Y-%m-%d' ) ) = 1 ) AS a,	( SELECT DISTINCT user_id, app_id, addtime FROM sdk_user_login_log WHERE DATEDIFF( now(), DATE_FORMAT( addtime, '%Y-%m-%d' ) ) in (2,3,4,5,6,7,15,30,60,90) ) AS b WHERE a.user_id = b.user_id AND a.app_id = b.app_id ) AS temp GROUP BY tim,temp.app_id";
		$list = Db::query($sql);
		foreach($list as $key => $value) {
			$where['day_register'] = $value['tim'];
			$where['app_id'] = $value['app_id'];
			$where['type'] = "1";
			$daystr = "day".$value['days'];
			$num = $value['num'];
			$re = UserKeepDay::where($where)->update([$daystr=>$num]);
		}
	}
	
	public function initUserRegisterDayKeepAll() {
		$begin = "2021-03-08";
		$end   = "2021-06-29";
		$begintime = strtotime($begin);$endtime = strtotime($end);
		for ($start = $begintime; $start <= $endtime; $start += 24*3600) {
			$stime = date("Y-m-d", $start);
			$sql = "SELECT DATE_FORMAT( temp.createtime, '%Y-%m-%d' ) AS tim,temp.app_id,count(*) AS num,DATEDIFF( DATE_FORMAT( temp.addtime, '%Y-%m-%d' ), DATE_FORMAT( temp.createtime, '%Y-%m-%d' ) ) + 1 AS days FROM (SELECT b.*,a.addtime FROM (SELECT DISTINCT user_id, app_id, addtime FROM sdk_user_login_log WHERE DATEDIFF( '$stime', DATE_FORMAT( addtime, '%Y-%m-%d' ) ) = 1 ) AS a,( SELECT DISTINCT	id,app_id,DATE_FORMAT( from_unixtime( createtime ), '%Y-%m-%d' ) AS createtime FROM	sdk_user WHERE DATEDIFF( '$stime', DATE_FORMAT( from_unixtime( createtime ), '%Y-%m-%d' ) ) IN (2,3,4,5,6,7,15,30,60,90) ) AS b WHERE a.user_id = b.id AND a.app_id = b.app_id ) AS temp GROUP BY tim,temp.app_id";
			$list = Db::query($sql);
			foreach($list as $key => $value) {
				$where['day_register'] = $value['tim'];
				$where['app_id'] = $value['app_id'];
				$where['type'] = "0";
				$daystr = "day".$value['days'];
				$num = $value['num'];
				$re = UserKeepDay::where($where)->update([$daystr=>$num]);
			}
		}
	}
	
	public function initUserLoginDayKeepAll() {
		$begin = "2021-03-08";
		$end   = "2021-06-29";
		$begintime = strtotime($begin);$endtime = strtotime($end);
		for ($start = $begintime; $start <= $endtime; $start += 24*3600) {
			$stime = date("Y-m-d", $start);
			$sql = "SELECT DATE_FORMAT( temp.addtime, '%Y-%m-%d' ) AS tim,temp.app_id,count(*) AS num,DATEDIFF( DATE_FORMAT( temp.atime, '%Y-%m-%d' ), DATE_FORMAT( temp.addtime, '%Y-%m-%d' ) ) + 1 AS days FROM (SELECT	b.*,a.addtime AS atime FROM ( SELECT DISTINCT user_id, app_id, addtime FROM sdk_user_login_log WHERE DATEDIFF( '$stime', DATE_FORMAT( addtime, '%Y-%m-%d' ) ) = 1 ) AS a,	( SELECT DISTINCT user_id, app_id, addtime FROM sdk_user_login_log WHERE DATEDIFF( '$stime', DATE_FORMAT( addtime, '%Y-%m-%d' ) ) in (2,3,4,5,6,7,15,30,60,90) ) AS b WHERE a.user_id = b.user_id AND a.app_id = b.app_id ) AS temp GROUP BY tim,temp.app_id";
			$list = Db::query($sql);
			foreach($list as $key => $value) {
				$where['day_register'] = $value['tim'];
				$where['app_id'] = $value['app_id'];
				$where['type'] = "1";
				$daystr = "day".$value['days'];
				$num = $value['num'];
				$re = UserKeepDay::where($where)->update([$daystr=>$num]);
			}
		}
	}
	
	public function initScaleAll() {
		$list = [2,3,4,5,6,7,15,30,60,90];
		foreach($list as $key => $value) {
			$sql = "UPDATE sdk_user_keep_day SET scale$value = day$value / num where scale$value is null and  day$value is not null";
			$re = Db::execute($sql);
		}
	}
    //计算留存率
    public function keep()
    {
        $appList = Db::name('app')->where('status', 0)->column('app_id');


       /* $s = strtotime('2021-09-28');
        $e = strtotime('2021-11-03');
        $d = ($e-$s)/(24*3600);
        for ($i = 1; $i <= $d; $i++) {
            $date = date('Y-m-d', $s + $i*24*3600);
            foreach ($appList as  $id) {
                $this->compute($date, $id);
                $this->compute($date, $id, 1);
            }

        }*/
        $date = date('Y-m-d',strtotime('yesterday'));
        foreach ($appList as  $id) {
            $this->compute($date, $id);
            $this->compute($date, $id, 1);
        }

    }
    //$type为0是新增，值为1是活跃
    //$date 日期
    private function compute($date, $app_id, $type = 0)
    {
        $dateList = [2,3,4,5,6,7,15,30,60,90];
        $strTime = strtotime($date);


        $dateTime = date('Y-m-d H:i:s', $strTime);
        //该日登录活跃的用户
        $dateUer = Db::name('user_login_log')->where('addtime','between', [$dateTime, $date.' 23:59:59'])->where('app_id', $app_id)->column('distinct user_id');
        if ($type == 0) {
            //新增用户
            $user = Db::name('user')->where('createtime','between', [$strTime, strtotime($date.' 23:59:59')])->where('app_id', $app_id)->column('id');
        } else {
            //活跃用户
            $user = $dateUer;
        }

        UserKeepDay::insert(array(
            'day_register' => $date,
            'app_id' => $app_id,
            'num' => count($user),
            'type' => $type,
            'createtime' => time()
        ));

        //循环要计算留存的日期
        foreach ($dateList  as $v) {
            $i = $v - 1;
            //留存日期
            $day_register = date('Y-m-d', $strTime - $i * 24 * 60 * 60);

            $day = "day{$v}";
            $scale = "scale{$v}";

            //判断留存日期是否存在新增或者登录活跃的用户
            $ret = Db::name('user_keep_day')->where('day_register', $day_register)->where('app_id', $app_id)->where('type', $type)->find();


            if (!$ret || $ret[$day] != null) {
                continue;
            }

            if ($type == 0) {
                //留存日的新增用户
                $keepUser = Db::name('user')->where('createtime','between', [strtotime($day_register), strtotime($day_register.' 23:59:59')])->where('app_id', $app_id)->column('id');

            } else {
                //留存日的登录活跃用户
                $keepUser = Db::name('user_login_log')->where('addtime','between', [date('Y-m-d H:i:s', strtotime($day_register)), date('Y-m-d H:i:s', strtotime($day_register.' 23:59:59') )])->where('app_id', $app_id)->column('distinct user_id');
            }

            //留存人数，留存人数等于当日新增或者活跃的用户，在date日期这一天继续登录活跃的人数
            if (empty($keepUser) || empty($dateUer)) {
                $data[$day] = 0;
                $data[$scale] = 0;
            } else {
                //当日留存的人数
                $num = count(array_intersect($keepUser, $dateUer));
                $data[$day] = $num;

                //留存率登录date日期登录的人除以留存日的登录用户或者新增用户

                if ($num == 0 || $ret['num'] == 0) {
                    $data[$scale] = 0;
                } else {
                    $data[$scale] = $num/$ret['num'];
                }

            }

            Db::name('user_keep_day')->where('id', $ret['id'])->update($data);
            unset($data);

        }
    }
}